using System;
using System.IO;
using System.Data;
using System.Reflection;
using System.Collections.Generic;

namespace Sqlpager
{
	public abstract class DbPage
	{
		#region Members
		
		int mFirst;				// first record
		int mLast;				// last record
		int mTotal;				// total number of records
		int mPage;				// current page
		protected string[] mColumnList;	
		protected Dictionary<string,string> mDic;
		protected int mPrevious;		// previous record number
		protected string mColumns;		// column list comma separated				
		protected int mRecords;			// records per page
		protected string mTable;			
		protected string mConnString;
		protected bool mConnected;
		protected string mError;
		protected string mSql;			// sql string to search
		protected string mWhereSql;		// sql where part
		protected string mOrderbySql;	// sql order by part
		protected string mSearchText;	// string to search
		
		#endregion
		
		#region Properties
		
		public int Page{get{return mPage;}}
		public int FirstRecord{get{return mFirst;}}
		public int LastRecord{get{return mLast;}}
		public int Total{get{return mTotal;}}
		public bool IsConnected{get{return mConnected;}}
		public string Error{get{return mError;}}
		public Dictionary<string,string> Settings
		{
			get{return mDic;}
		}
		
		#endregion
		
		#region Constructors
		
		public DbPage(string server, string user, 
		              string password,string database,
		              string remember)		
		{
			mDic = new Dictionary<string,string>();
			mDic.Add("server",server);
			mDic.Add("user",user);
			mDic.Add("password",password);
			mDic.Add("database",database);
			mDic.Add("remember",remember);
			mConnected = false;
			SetConnectionString();
			//Connect();
		}
		public DbPage(string server, string user, 
		              string password)		
		{
			mDic = new Dictionary<string,string>();
			mDic.Add("server",server);
			mDic.Add("user",user);
			mDic.Add("password",password);
			SetConnectionString();
			//Connect();
		}
		
		public DbPage(string table, string columns)		
		{
			LoadSettings();
			mTable = table;
			mColumns = columns;
			mColumnList = columns.Split(new char[]{','});	
			mRecords = 100;
			mFirst = 1;
			mLast = 0;
			mTotal = 0;
			mPrevious = 0;
			mSearchText = "";	
		}
		
		#endregion
		
		#region Abstract Methods
		
		protected abstract int GetTotal();
		protected abstract string CreateSql();
		protected abstract string CreateWhereSql();
		public abstract DataTable GetData(string sql);
		public abstract bool Connect();
		public abstract int Execute(string sql);
		public abstract void SetConnectionString();
		public abstract Ticket Select(int id);
		public abstract void Insert(Ticket t);
		public abstract void Update(Ticket t);
		public abstract void Delete(int id);
		
		#endregion
		
		#region Public Methods
		
		public bool FirstExists()
		{
			return (mPrevious >= mRecords) ? true : false;
		}
		
		public bool PreviousExists()
		{
			return (mPrevious >= 2 * mRecords) ? true : false;
		}
		
		public bool NextExists()
		{
			return (mPrevious < mTotal - 2 * mRecords) ?
					true : false;
		}
		
		public bool LastExists()
		{
			return (mPrevious < mTotal - mRecords) ? true : false;
		}
		
		public int PreviousPage()
		{
			return PreviousExists() ? mPage - 1 : mPage;
		}
		
		public int NextPage()
		{
			return NextExists() ? mPage + 1 : mPage;
		}
		
		public int LastPage()
		{
			return (int)Math.Ceiling(mTotal / (mRecords * 1.0f));
		}

		public DataTable Search(string text, 
								int page, 
								string orderby)
		{
			mSearchText = EscapeString(text);
			mWhereSql = "";
			if(mSearchText != "")			
				mWhereSql = CreateWhereSql();
			mOrderbySql = String.IsNullOrEmpty(orderby) ? 
				mColumnList[0] : orderby;	
			mTotal = GetTotal();
			mPage = LastPage();
			if (page != 0 && page < mPage)
				mPage = page;
			mPrevious = (mPage - 1) * mRecords;
			mFirst = mPrevious + 1;
			mLast = mPrevious + mRecords;
			if (mLast > mTotal)
				mLast = mTotal;
			DataTable dt = null;
			if (IsValid()){
				mSql = CreateSql();
				dt = GetData(mSql);
			}
			return dt;
		}
		
		public void LoadSettings()
		{
			mDic = new Dictionary<string, string>();
			mDic.Add("server","");
			mDic.Add("database","");
			mDic.Add("user","");
			mDic.Add("password","");
			mDic.Add("remember","");
			try {
				string path = Path.GetDirectoryName(
				    Assembly.GetExecutingAssembly().Location);
				string file = Path.Combine(path,"db.txt");
				string[] lines = File.ReadAllLines(file);				
				foreach(string line in lines) {
					string[] aux = line.Split(new char[]{'='});
					if(mDic.ContainsKey(aux[0].Trim()))
						mDic[aux[0].Trim()] = aux[1].Trim();
				}
				SetConnectionString();
			} catch(Exception ex){
				Console.WriteLine("Can't create connection string\n"+ex.Message);
			}
			
		}
		public void SaveSettings()
		{
			StreamWriter w = null;
			try {
				w = new StreamWriter("db.txt");	
				bool savepassword = (mDic["remember"]=="1")?true:false;
				foreach(KeyValuePair<string,string> setting in mDic)
					if(setting.Key!="password")
						w.WriteLine(setting.Key+" = "+setting.Value);
					else if(savepassword) 
						w.WriteLine(setting.Key+" = "+setting.Value);
			} catch(Exception ex){
				Console.WriteLine("Can't save settings\n"+ex.Message);
			}
			w.Close();
			w.Dispose();
		}
		
		#endregion
		
		#region Private Methods

		bool IsValid()
		{
			return (mTotal > 0 && mTotal >= mPrevious &&
					(mPrevious % mRecords == 0));
		}

		string EscapeString(string text)
		{   
			if(!String.IsNullOrEmpty(text))
				return text.Replace("'", "''");
			return text;
		}	
		
		#endregion
		
	}
}
